﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using sharpPDF;
using sharpPDF.Enumerators;
using sharpPDF.Fonts;
using sharpPDF.Elements;
using sharpPDF.PDFControls;
using sharpPDF.Tables;

namespace Projekt
{
    /// <summary>
    /// Szamlakkal kapcsolato muveletek
    /// </summary>
    class Bill
    {
        private static Bill instance = null;
        static readonly object padlock = new object();
        Dalgen conn = Dalgen.Instance;
        
        Bill() { }

        /// <summary>
        /// Megnezi, hogy letezik e mar peldany belole es ha letezik, visszakuld egy referencia ra, kulonben letrehoz egyet
        /// </summary>
        public static Bill Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new Bill();
                        return instance;
                    }
                    //return instance;
                    return null;
                }
            }
        }

        /// <summary>
        /// kitorli a letezo peldanyt
        /// </summary>
        public static Bill disposeInstance
        {
            get
            {
                lock (padlock)
                {
                    instance = null;
                }
                return null;
            }
        }

        /// <summary>
        /// Lekeri a kovetkezo receptio(Nir) szamat
        /// </summary>
        /// <returns>a kovetkezo recceptio(Nir) sorszama</returns>
        public int getRecNr()
        {
            try
            {
                String sqlQuery = "SELECT MAX(NirNR) FROM Nir";
                return ((int)conn.ScalarCmd(sqlQuery))+1;
            }
            catch (Exception e) { throw e; }
        }

        /// <summary>
        /// Hozzaad egy uj fakturat az adatbazishoz
        /// </summary>
        /// <param name="CUI">A beszalito azonositoja</param>
        /// <param name="BillNR">A faktura szama</param>
        /// <param name="BillDate">A fakturazas datuma</param>
        /// <param name="BankAccount">A beszalito bankszamla szama</param>
        /// <param name="nirLines">A faktura sorai</param>
        /// <param name="RecNr">receptio szama</param>
        /// <param name="RecDate">receptio datuma</param>
        public void addBill(String CUI, String BillNR, String BillDate, String BankAccount, DataTable nirLines, String RecNr, String RecDate)
        {
            try
            {
                String sqlQuery = "INsert into NIR(NirNr,NSorszam,NDatum,NSzamlaSzam,NCegID) values('" + RecNr + "','" + BillNR + "','" + BillDate + "','" + BankAccount + "','" + CUI + "')";
                conn.nonQueryCmd(sqlQuery);
                String sqlQueryBase = "Insert into NirSorok(NirNr,NAruId,NMennyiseg,NAr) values('" + RecNr + "','";
                Produce aru = new Produce();
                foreach (DataRow rows in nirLines.Rows)
                {
                    int id = aru.getID(rows[0].ToString(), rows[1].ToString());
                    sqlQuery = sqlQueryBase + id + "','" + rows[2] + "','" + rows[3] + "')";
                    conn.nonQueryCmd(sqlQuery);
                }
            }
            catch (Exception es) { throw es; }

        }

        /// <summary>
        /// Lekeri a szamlak listajat a parameterben kapott szurok alapjan
        /// </summary>
        /// <param name="nirNr">receptio szama</param>
        /// <param name="supplierName">beszalito neve</param>
        /// <param name="billDate">szamlazas datuma</param>
        /// <param name="billNR">szamla sorszama</param>
        /// <returns>A parameterben kapott szurok alapjan lekert szamlak tablaja</returns>
        public DataTable getBillList(String nirNr, String supplierName, String billDate, String billNR)
        {
            try
            {
                string sqlQuery = "Select CegID from cegek where CNev = '" + supplierName + "'";
                Object cui = conn.ScalarCmd(sqlQuery);
                if (cui == null) cui = "";
                sqlQuery = "Select NirNr, CNev, NDatum, Nsorszam From Nir,Cegek Where nir.NCegId = Cegek.CegId and nirNr like '" + nirNr + "%' And NCegID like '" + cui.ToString() + "%' and NDatum like '" + billDate + "%' and NSorszam like '" + billNR +"%'";
                return conn.QueryCmd(sqlQuery);
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Lekeri egy NIR sorait
        /// </summary>
        /// <param name="nirNr">NIR sorszama</param>
        /// <returns>a NIR sorai(DataTable)</returns>
        public DataTable getNirlines(string nirNr)
        {
            try
            {
                string sqlQuery = "select Distinct ANev as produceName,MNev as UM,NMennyiseg as Quantity,NAr as Prize,BAr as SellPrize from Aruk,NirSorok,BonSorok, Mertekegyseg, bon where NirSorok.NirNR = '" + nirNr + "' and Aruk.AMertekegyseg = Mertekegyseg.Id and  Bon.BonNR = BonSorok.BonNR and NirNR = Bon.BNirNR and NAruID = AruID and BonSorok.BAruID = AruID";
                return conn.QueryCmd(sqlQuery);
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Levagja a szamokat 2 tizedes pontossagra
        /// </summary>
        /// <param name="value">A szam</param>
        /// <returns>A szam 2 tizedes pontossaggal</returns>
        private String trunc(String value)
        {
            return Math.Round(Convert.ToDouble(value), 2).ToString();
        }

        /// <summary>
        /// Torol egy NIR-t
        /// </summary>
        /// <param name="nirNr">Nir szama</param>
        public void deleteBill(String nirNr)
        {
            try
            {
                conn.deleteNir(nirNr);
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Kiirja a NIR-t pdf-be(nyomtatasra)
        /// </summary>
        /// <param name="nirNR">Nir sorszama</param>
        public void writeToPDF(int nirNR = -1)
        {
            try
            {
                string sqlQuery;
                if (nirNR == -1)
                {
                    sqlQuery = "Select max(nirNR) from Nir";
                    nirNR = (int)conn.ScalarCmd(sqlQuery);
                }
                string firmName;
                string location;
                string NirDate;
                string RecDate;
                string faktnr;
                string supplierName;
                string cui;
                int tva;
                string valCump;
                string tvaCump;
                string valVanz;


                sqlQuery = "Select Ertek from bealitasok where nev = 'TVA'";
                tva = Convert.ToInt32(conn.ScalarCmd(sqlQuery).ToString());
                sqlQuery = "Select Ertek from bealitasok where nev = 'CegNev'";
                firmName = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select Ertek from bealitasok where nev = 'Helyseg'";
                location = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select NDatum from Nir where NirNr = '" + nirNR + "'";
                NirDate = conn.ScalarCmd(sqlQuery).ToString();
           //     NirDate.Remove(NirDate.IndexOf(" "));
                sqlQuery = "Select BDatum from Bon where BNirNr = '" + nirNR + "'";
                RecDate = conn.ScalarCmd(sqlQuery).ToString();
           //     RecDate.Remove(RecDate.IndexOf(" "));
                sqlQuery = "Select Nsorszam from Nir Where NirNr = '" + nirNR +"'";
                faktnr = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select CNev from Cegek,Nir Where NCegID = CegID and NirNr = '" + nirNR + "'";
                supplierName = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select NCegId from nir where NirNr = '" + nirNR + "'";
                cui = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select Nertek From nirErtekek where NirNr = '" + nirNR + "' and NertekTipus = '0'";
                valCump = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select Nertek From nirErtekek where NirNr = '" + nirNR + "' and NertekTipus = '1'";
                tvaCump = conn.ScalarCmd(sqlQuery).ToString();
                sqlQuery = "Select SUM(Bertek) from BonErtekek,Bon where Bon.BonNR = BonErtekek.BonNR and Bon.BNirNR = '" + nirNR + "'";
                valVanz = conn.ScalarCmd(sqlQuery).ToString();
                Single tvaVanz = Convert.ToSingle(valVanz) - (Convert.ToSingle(valVanz) / Convert.ToSingle(1.24));


                string file = nirNR.ToString() + DateTime.Today.Date;
                if (File.Exists(nirNR.ToString()))
                    File.Delete(nirNR.ToString());


                pdfDocument mypdf = new pdfDocument(nirNR.ToString() , "ReB");
                pdfPage myPage = mypdf.addPage(700,500);
                myPage.addText(firmName, 30, 680, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText(location, 30, 670, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("NOTA DE RECEPTIE NR: " + nirNR + " DIN DATA: " + RecDate, 150, 660, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.drawLine(0, 650, 500, 650, predefinedLineStyle.csNormal);
                myPage.addText("DOCUMENT : fact/aviz nr " + faktnr + " din " + NirDate, 30, 640, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Furnizorul : " + supplierName + ", c.f. : " + cui, 30, 630, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.drawLine(0, 620, 500, 620);

                int[] koordx = new int[10] {30,60,130,160,200,240,290,330,370,410};

                myPage.addText("Nr. crt.", koordx[0], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Sortiment", koordx[1], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("UM", koordx[2], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Cantitate", koordx[3], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Pret(C)", koordx[4], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Valoare(C).", koordx[5], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("TVA(C)", koordx[6], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Pret(V)", koordx[7], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("Valoare(V)", koordx[8], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText("TVA(V)", koordx[9], 610, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.drawLine(0, 600, 500, 600);

                DataTable nirLines = new DataTable();
                nirLines = getNirlines(nirNR.ToString());
                int y = 600;
                int line = 1;
                foreach (DataRow rows in nirLines.Rows)
                {
                    y -= 10;
                    myPage.addText(line.ToString(), koordx[0], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    line++;
                    myPage.addText(rows[0].ToString(), koordx[1], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(rows[1].ToString(), koordx[2], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(rows[2].ToString(), koordx[3], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(trunc(rows[3].ToString()), koordx[4], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(trunc((Convert.ToSingle(rows[2]) * Convert.ToSingle(rows[3])).ToString()), koordx[5], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(trunc((Convert.ToSingle(rows[2]) * Convert.ToSingle(rows[3]) * 24 / 100).ToString()), koordx[6], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(trunc(rows[4].ToString()), koordx[7], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(trunc((Convert.ToSingle(rows[2]) * Convert.ToSingle(rows[4])).ToString()), koordx[8], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                    myPage.addText(trunc((Convert.ToSingle(rows[2]) * (Convert.ToSingle(rows[4]) - (Convert.ToSingle(rows[4]) / 1.24))).ToString()), koordx[9], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                }
                y -= 10;
                myPage.drawLine(0, y, 500, y);
                y -= 10;
                
                myPage.addText("Total valoric al receptiei: ", koordx[0], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText((Math.Round(Convert.ToDouble(valCump),2)).ToString(), koordx[5], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText((Math.Round(Convert.ToDouble(tvaCump),2)).ToString(), koordx[6], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText((Math.Round(Convert.ToDouble(valVanz),2)).ToString(), koordx[8], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                myPage.addText((Math.Round(Convert.ToDouble(tvaVanz),2)).ToString(), koordx[9], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                y -= 10;
                myPage.drawLine(0, y, 500, y);
                y -= 10;
                myPage.addText("Faktura are valoare de: " + (Convert.ToSingle(valCump)+Convert.ToSingle(tvaCump)).ToString(), koordx[0], y, mypdf.getFontReference(predefinedFont.csHelvetica), 6);
                mypdf.createPDF(@"c:\pdf\" + nirNR.ToString() + ".PDF");
                myPage = null;
                mypdf = null;
                System.Diagnostics.Process.Start(@"c:\pdf\" + nirNR + ".PDF");
                System.Threading.Thread.Sleep(250);

            }
            catch(Exception es) {throw es;}
        }

        /// <summary>
        /// Lekeri egy NIR szamlazasi datumat
        /// </summary>
        /// <param name="nirNr">Nir sorszama</param>
        /// <returns>az adott NIR-hez tartozo szamla datuma</returns>
        public string getBillDate(string nirNr)
        {
            try
            {
                string sqlQuery = "Select NDatum from Nir where NirNr = '" + nirNr + "'";
                return conn.ScalarCmd(sqlQuery).ToString();
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Lekeri egy Nir tulajdonosat(beszalito)
        /// </summary>
        /// <param name="nirNr">Nir sorszama</param>
        /// <returns>a Nir-hez tartozo szamla melyik cegtol szarmazik</returns>
        public string getSupplierName(String nirNr)
        {
            try
            {
                string sqlQuery = "Select CNev from Nir,Cegek where NirNr = '" + nirNr + "' and NCegID = Cegek.CegID" ;
                return conn.ScalarCmd(sqlQuery).ToString();
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Lekeri egy Nir tulajdonosanak(beszalito) azonositojat(cui)
        /// </summary>
        /// <param name="nirNr">Nir sorszama</param>
        /// <returns>a Nir-hez tartozo szamla beszalitojanak azonositoja</returns>
        public string getSupplierCui(String NirNr)
        {
            try
            {
                string sqlQuery = "Select NCegID from Nir where NirNR = '" + NirNr + "'";
                return conn.ScalarCmd(sqlQuery).ToString();
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Lekeri egy adott Nir hez tartozo szamla szamat
        /// </summary>
        /// <param name="nirNr">Nir Sorszama</param>
        public string getBillnr(string nirNr)
        {
            try
            {
                string sqlQuery = "Select NSorszam from Nir where NirNR = '" + nirNr + "'";
                return conn.ScalarCmd(sqlQuery).ToString();
            }
            catch (Exception es) { throw es; }
        }

        /// <summary>
        /// Lekeri egy adott Nir-hez tartozo szamla tulajdonosanak(beszalito) bankszamlaszamat
        /// </summary>
        /// <param name="nirNr">Nir sorszama</param>
        /// <returns>az adott Nir-hez tartozo szamla tulajdonosanak(beszalito) bankszamlaszama</returns>
        public string getBancAccount(string nirNr)
        {
            try
            {
                string sqlQuery = "Select NSzamlaSzam from Nir where NirNR = '" + nirNr + "'";
                return conn.ScalarCmd(sqlQuery).ToString();
            }
            catch (Exception es) { throw es; }
        }
    }
    
    
}